import numpy as np
import pandas as pd
import geopandas as gpd
import fiona
import glob
import os
import contextily as ctx
from scipy.spatial import cKDTree
from shapely.geometry import Point
import json
from tqdm.auto import tqdm
pd.set_option('min_rows', 30)
import sys
sys.path.append('..')
from importlib import reload
# import src.utils as utils
# reload(utils)
import matplotlib.pyplot as plt
plt.rcParams['figure.figsize'] = (12, 12)
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', 10)
%%time
## read in and get bounds
df = pd.read_csv("input/BCs_issued_by_AUP_TLADCs_2021FEB.csv")
bcs = gpd.GeoDataFrame(df, geometry=gpd.points_from_xy(df.X_Coordinate, df.Y_Coordinate))
# get bounds of data set (with a small buffer), useful for clipping other datasets
bcs = bcs.set_crs(epsg=4326)
bounds4326 = bcs.total_bounds
bcs = bcs.to_crs(epsg=3857)
bounds3857 = bcs.total_bounds
CPU times: user 37.2 s, sys: 859 ms, total: 38 s Wall time: 38 s
## add small buffer to bounds
x_delta3857 = bounds3857[2] - bounds3857[0]
y_delta3857 = bounds3857[3] - bounds3857[1]
bounds_buffered3857 = [bounds3857[0] - y_delta3857 / 200, bounds3857[1] - x_delta3857 / 200, bounds3857[2] + y_delta3857 / 200, bounds3857[3] + x_delta3857 / 200]
bounds_buffered3857
x_delta4326 = bounds4326[2] - bounds4326[0]
y_delta4326 = bounds4326[3] - bounds4326[1]
bounds_buffered4326 = [bounds4326[0] - y_delta4326 / 200, bounds4326[1] - x_delta4326 / 200, bounds4326[2] + y_delta4326 / 200, bounds4326[3] + x_delta4326 / 200]
bounds_buffered4326
[174.15865864485, -37.2937968355, 175.54295485515, -35.877583094500004]
# one address has no ADDRESS_1, but an ADDRESS_2 with a leading digit
display(bcs[bcs.ADDRESS_1.isna() & ~bcs.ADDRESS_2.isna()][bcs[bcs.ADDRESS_1.isna() & ~bcs.ADDRESS_2.isna()].ADDRESS_2.str.contains('^[0-9]', regex=True)])
# move address 2 to address 1 for that one BC...
bcs.loc[bcs.OBS == 146813, 'ADDRESS_1'] = "25 HOLLYFORD DRV"
bcs.loc[bcs.OBS == 146813, 'ADDRESS_2'] = ""
| OBS | CONSENT_DATE | MARCH_YEAR | ADDRESS_1 | ADDRESS_2 | ADDRESS_3 | LB_Name | BUILDING_TYPE_NAME | BUILDING_TYPE_CODE | FLOOR_AREA | VALUE | BUILDINGS | Building_Type_Group | Residential_Type | Business_Group | Business_Category | Type_Class_Year | MBCODE | MBYEAR | AUP_BaseZone | AUP_BaseZone_Group | ZONE | ZONE_ID | ZONE_NAME | ZONE_TYPE | BUSINESS_TYPE | PlanAreaName | SHA_Name_154 | X_Coordinate | Y_Coordinate | geometry | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 64127 | 146813 | Sep 01, 2013 12:00:00 AM | Mar 01, 2013 12:00:00 AM | NaN | 25 HOLLYFORD DRV | CLOVER PARK | Howick | New (and pre-built) house, unit, bach, crib, b... | 1100 | 239.0 | 240000.0 | 1 | Residential | Detached | NaN | NaN | 1998 | 716014.0 | 2013.0 | Residential - Mixed Housing Suburban Zone | Residential | 18.0 | MCC_MR | Main Residential | Residential | NaN | NaN | NaN | 174.896997 | -36.986932 | POINT (19469444.625 -4437285.457) |
%%time
# get number and name of street (but not 'road', 'street', 'place' etc), for matching with addresses
def number_name_bc(x):
"""extract street number and first complete word of the street name from building consents"""
if x.ADDRESS_1 is None:
pass
else:
# get number and first word of address
joined_address = ' '.join([str(x[f'ADDRESS_{i}']) for i in [1,2, 3]]).lower()
return ' '.join(joined_address.split(' ')[:2])
def full_address_bc(x):
"""extract full address from building consents"""
if x.ADDRESS_1 is None:
pass
else:
# get number and first word of address
joined_address = ' '.join([str(x[f'ADDRESS_{i}']) for i in [1,2, 3] if not str(x[f'ADDRESS_{i}']) == 'nan']).lower()
return joined_address
bcs['number_name'] = bcs.apply(number_name_bc, axis=1)
bcs['full_address'] = bcs.apply(full_address_bc, axis=1)
# to have successfully extracted the name as the second 'word': the last character is never numeric
# print(sum(bcs['number_name'].apply(lambda x: x[-1] in [i for i in range(10)])))
# there will still be some cases where there is no street number
display(bcs.number_name.sample(5))
22947 45 station 65398 22a stottholm 5621 19 ventura 149709 3 tainui 6642 111 kuaka Name: number_name, dtype: object
CPU times: user 28.9 s, sys: 500 ms, total: 29.4 s Wall time: 29.4 s
ax = bcs.sample(3000).plot(column='Building_Type_Group', legend=True, figsize=(20,20))
ctx.add_basemap(ax)
# split into three branches: those with an address number range (e.g. 10-20), those with a single number (e.g. 10), and those with no number
# those with no number cannot be matched with addresses to get a more accuract coordinate
bcs_non_na = bcs[~bcs.ADDRESS_1.isna()]
bcs_ranged = bcs_non_na[bcs_non_na.ADDRESS_1.str.contains('^[0-9]+-[0-9]', regex=True)]
# get all bcs which start with a digit
bcs_numbered = bcs_non_na[bcs_non_na.ADDRESS_1.str.contains('^[0-9]', regex=True)]
# exclude those in bcs_ranged
bcs_numbered = bcs_numbered[~bcs_numbered.ADDRESS_1.str.contains('^[0-9]+-[0-9]', regex=True)]
bcs_others = pd.concat([bcs_non_na[~bcs_non_na.ADDRESS_1.str.contains('^[0-9]', regex=True)], bcs[bcs.ADDRESS_1.isna()]])
print('non na:', len(bcs_non_na))
print('1. ranged:', len(bcs_ranged))
print('2. numbered:', len(bcs_numbered))
print('3. non ranged/non numbered:', len(bcs_others))
print('total bcs:', len(bcs))
print('sum of 1, 2, 3:', len(bcs_ranged) + len(bcs_numbered) + len(bcs_others))
non na: 193005 1. ranged: 6267 2. numbered: 183013 3. non ranged/non numbered: 17946 total bcs: 207226 sum of 1, 2, 3: 207226
# all ranges that have a non digit in them
rows = []
for index, row in tqdm(bcs_ranged.iterrows(), total=len(bcs_ranged)):
r = row.number_name.split(' ')[0].split('/')[0]
if not all([s.isdecimal() for s in r.split('-')]):
print(row.number_name.split(' ')[0])
rows.append(row)
1-1a 2-2a 9-9a 1-1a 3-5a 3-5a
# addressing of building consents is very inconsistent
# some building consents have street name in ADDRESS_2, some have suburb, some even have 'Auckland'
bcs[bcs.ADDRESS_2 == 'Auckland'].sample(5)
| OBS | CONSENT_DATE | MARCH_YEAR | ADDRESS_1 | ADDRESS_2 | ADDRESS_3 | LB_Name | BUILDING_TYPE_NAME | BUILDING_TYPE_CODE | FLOOR_AREA | VALUE | BUILDINGS | Building_Type_Group | Residential_Type | Business_Group | Business_Category | Type_Class_Year | MBCODE | MBYEAR | AUP_BaseZone | AUP_BaseZone_Group | ZONE | ZONE_ID | ZONE_NAME | ZONE_TYPE | BUSINESS_TYPE | PlanAreaName | SHA_Name_154 | X_Coordinate | Y_Coordinate | geometry | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 24601 | 188214 | Nov 01, 2018 12:00:00 AM | Mar 01, 2019 12:00:00 AM | 528 DON BUCK ROAD | Auckland | NaN | Henderson - Massey | Non-building construction | 3111 | 0.0 | 40000.0 | 1 | Other | NaN | NaN | NaN | 2014 | 226402.0 | 2013.0 | Residential - Mixed Housing Urban Zone | Residential | 60.0 | WCC_6 | Foothills | Rural | NaN | Redhills (Fred Taylor Drive) � Stage 1 | Redhills (Fred Taylor Drive) � Stage 1 | 174.603215 | -36.824411 | POINT (19436740.940 -4414660.154) |
| 24605 | 188218 | Nov 01, 2018 12:00:00 AM | Mar 01, 2019 12:00:00 AM | 528 DON BUCK ROAD | Auckland | NaN | Henderson - Massey | Townhouses, flats, units, and other dwellings | 1129 | 129.0 | 233100.0 | 1 | Residential | Attached | NaN | NaN | 2014 | 226402.0 | 2013.0 | Residential - Mixed Housing Urban Zone | Residential | 60.0 | WCC_6 | Foothills | Rural | NaN | Redhills (Fred Taylor Drive) � Stage 1 | Redhills (Fred Taylor Drive) � Stage 1 | 174.603215 | -36.824411 | POINT (19436740.940 -4414660.154) |
| 24606 | 188219 | Nov 01, 2018 12:00:00 AM | Mar 01, 2019 12:00:00 AM | 528 DON BUCK ROAD | Auckland | NaN | Henderson - Massey | Townhouses, flats, units, and other dwellings | 1129 | 104.0 | 188640.0 | 1 | Residential | Attached | NaN | NaN | 2014 | 226402.0 | 2013.0 | Residential - Mixed Housing Urban Zone | Residential | 60.0 | WCC_6 | Foothills | Rural | NaN | Redhills (Fred Taylor Drive) � Stage 1 | Redhills (Fred Taylor Drive) � Stage 1 | 174.603215 | -36.824411 | POINT (19436740.940 -4414660.154) |
| 24614 | 188227 | Nov 01, 2018 12:00:00 AM | Mar 01, 2019 12:00:00 AM | 524-526 Don Buck Road | Auckland | NaN | Henderson - Massey | Townhouses, flats, units, and other dwellings | 1129 | 135.0 | 216000.0 | 1 | Residential | Attached | NaN | NaN | 2014 | 226403.0 | 2013.0 | Residential - Mixed Housing Urban Zone | Residential | 60.0 | WCC_7 | Living | Residential | NaN | Redhills (Fred Taylor Drive) � Stage 1 | Redhills (Fred Taylor Drive) � Stage 1 | 174.607308 | -36.824848 | POINT (19437196.626 -4414720.873) |
| 24618 | 188231 | Nov 01, 2018 12:00:00 AM | Mar 01, 2019 12:00:00 AM | 528 DON BUCK ROAD | Auckland | NaN | Henderson - Massey | Townhouses, flats, units, and other dwellings | 1129 | 136.0 | 245622.0 | 1 | Residential | Attached | NaN | NaN | 2014 | 226402.0 | 2013.0 | Residential - Mixed Housing Urban Zone | Residential | 60.0 | WCC_6 | Foothills | Rural | NaN | Redhills (Fred Taylor Drive) � Stage 1 | Redhills (Fred Taylor Drive) � Stage 1 | 174.603215 | -36.824411 | POINT (19436740.940 -4414660.154) |
%%time
## read in address dataset and add number_name, just like for BCs
# https://data.linz.govt.nz/layer/53353-nz-street-address/
addresses = gpd.read_file('input/lds-nz-street-address-GPKG-CLIPPED.gpkg').to_crs(3857)
def number_name_addresses(x):
return ' '.join(x.full_address.split(' ')[:2]).lower()
addresses['number_name'] = addresses.apply(number_name_addresses, axis=1)
/data/miniconda3/envs/house-upzone/lib/python3.8/site-packages/geopandas/geodataframe.py:577: RuntimeWarning: Sequential read of iterator was interrupted. Resetting iterator. This can negatively impact the performance. for feature in features_lst:
CPU times: user 2min 5s, sys: 11 s, total: 2min 16s Wall time: 2min 16s
addresses[(addresses.address_number == 9) & (addresses.full_road_name == 'Hopetoun Street')].sample(3)
| address_id | change_id | address_type | unit_value | address_number | address_number_suffix | address_number_high | water_route_name | water_name | suburb_locality | town_city | full_address_number | full_road_name | full_address | road_section_id | gd2000_xcoord | gd2000_ycoord | water_route_name_ascii | water_name_ascii | suburb_locality_ascii | town_city_ascii | full_road_name_ascii | full_address_ascii | geometry | number_name | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 513953 | 1989299 | 2991221 | Road | 202 | 9 | None | NaN | None | None | Freemans Bay | Auckland | 202/9 | Hopetoun Street | 202/9 Hopetoun Street, Freemans Bay, Auckland | 189772 | 174.752374 | -36.858544 | None | None | Freemans Bay | Auckland | Hopetoun Street | 202/9 Hopetoun Street, Freemans Bay, Auckland | POINT (19453345.297 -4419407.917) | 202/9 hopetoun |
| 513851 | 1915403 | 2991240 | Road | 502 | 9 | None | NaN | None | None | Freemans Bay | Auckland | 502/9 | Hopetoun Street | 502/9 Hopetoun Street, Freemans Bay, Auckland | 189772 | 174.752374 | -36.858544 | None | None | Freemans Bay | Auckland | Hopetoun Street | 502/9 Hopetoun Street, Freemans Bay, Auckland | POINT (19453345.297 -4419407.917) | 502/9 hopetoun |
| 513854 | 1915406 | 2991245 | Road | 603 | 9 | None | NaN | None | None | Freemans Bay | Auckland | 603/9 | Hopetoun Street | 603/9 Hopetoun Street, Freemans Bay, Auckland | 189772 | 174.752374 | -36.858544 | None | None | Freemans Bay | Auckland | Hopetoun Street | 603/9 Hopetoun Street, Freemans Bay, Auckland | POINT (19453345.297 -4419407.917) | 603/9 hopetoun |
addresses.sample(10)
| address_id | change_id | address_type | unit_value | address_number | address_number_suffix | address_number_high | water_route_name | water_name | suburb_locality | town_city | full_address_number | full_road_name | full_address | road_section_id | gd2000_xcoord | gd2000_ycoord | water_route_name_ascii | water_name_ascii | suburb_locality_ascii | town_city_ascii | full_road_name_ascii | full_address_ascii | geometry | number_name | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 52975 | 1002801.0 | 889731.0 | Road | None | 2.0 | None | NaN | None | None | Titirangi | Auckland | 2 | Waerenga Place | 2 Waerenga Place, Titirangi, Auckland | 171349.0 | 174.653251 | -36.934016 | None | None | Titirangi | Auckland | Waerenga Place | 2 Waerenga Place, Titirangi, Auckland | POINT (19442311.009 -4429913.404) | 2 waerenga |
| 158403 | 998738.0 | 886362.0 | Road | None | 24.0 | None | NaN | None | None | Te Atatu South | Auckland | 24 | Tiroroa Avenue | 24 Tiroroa Avenue, Te Atatu South, Auckland | 191180.0 | 174.648857 | -36.869946 | None | None | Te Atatu South | Auckland | Tiroroa Avenue | 24 Tiroroa Avenue, Te Atatu South, Auckland | POINT (19441821.779 -4420994.377) | 24 tiroroa |
| 596494 | 2227003.0 | 5300930.0 | Road | 1702 | 10.0 | None | NaN | None | None | Auckland Central | Auckland | 1702/10 | Commerce Street | 1702/10 Commerce Street, Auckland Central, Auc... | 164395.0 | 174.768026 | -36.845663 | None | None | Auckland Central | Auckland | Commerce Street | 1702/10 Commerce Street, Auckland Central, Auc... | POINT (19455087.663 -4417615.980) | 1702/10 commerce |
| 318775 | 1921571.0 | 1745258.0 | Road | None | 7.0 | B | NaN | None | None | St Heliers | Auckland | 7B | Edison Place | 7B Edison Place, St Heliers, Auckland | 203321.0 | 174.843315 | -36.867704 | None | None | St Heliers | Auckland | Edison Place | 7B Edison Place, St Heliers, Auckland | POINT (19463468.830 -4420682.420) | 7b edison |
| 270718 | 1147783.0 | 1013962.0 | Road | None | 4.0 | None | NaN | None | None | Devonport | Auckland | 4 | Huia Street | 4 Huia Street, Devonport, Auckland | 192089.0 | 174.792322 | -36.830337 | None | None | Devonport | Auckland | Huia Street | 4 Huia Street, Devonport, Auckland | POINT (19457792.320 -4415484.318) | 4 huia |
| 124870 | 1878596.0 | 1834835.0 | Road | 1 | 95.0 | None | NaN | None | None | Papatoetoe | Auckland | 1/95 | Motatau Road | 1/95 Motatau Road, Papatoetoe, Auckland | 193672.0 | 174.863701 | -36.961238 | None | None | Papatoetoe | Auckland | Motatau Road | 1/95 Motatau Road, Papatoetoe, Auckland | POINT (19465738.129 -4433705.285) | 1/95 motatau |
| 94373 | 1487614.0 | 1295132.0 | Road | None | 18.0 | None | NaN | None | None | Riverhead | Riverhead | 18 | Maude Street | 18 Maude Street, Riverhead | 205312.0 | 174.593912 | -36.756725 | None | None | Riverhead | Riverhead | Maude Street | 18 Maude Street, Riverhead | POINT (19435705.379 -4405251.420) | 18 maude |
| 202231 | 1498243.0 | 1304879.0 | Road | None | 87.0 | None | NaN | None | None | Orewa | Orewa | 87 | Tauranga Place | 87 Tauranga Place, Orewa | 204608.0 | 174.674711 | -36.589828 | None | None | Orewa | Orewa | Tauranga Place | 87 Tauranga Place, Orewa | POINT (19444699.877 -4382087.267) | 87 tauranga |
| 227912 | 1226642.0 | 1082889.0 | Road | None | 12.0 | None | NaN | None | None | Clover Park | Auckland | 12 | Pesaro Place | 12 Pesaro Place, Clover Park, Auckland | 185178.0 | 174.888951 | -36.978107 | None | None | Clover Park | Auckland | Pesaro Place | 12 Pesaro Place, Clover Park, Auckland | POINT (19468548.989 -4436055.601) | 12 pesaro |
| 434079 | 1489551.0 | 1296876.0 | Road | None | 11.0 | None | NaN | None | None | Riverhead | Riverhead | 11 | Great North Road | 11 Great North Road, Riverhead | 167386.0 | 174.592630 | -36.759447 | None | None | Riverhead | Riverhead | Great North Road | 11 Great North Road, Riverhead | POINT (19435562.668 -4405629.650) | 11 great |
# and alternative matching would have been to take the digits + the first n characters of each address up to but not including 'road', drive', 'avenue' etc... but that would have been messy
set([x.split(' ')[-1] for x in addresses.full_road_name])
{'1',
'12',
'16',
'2',
'Access',
'Anchorage',
'Ara-Kotinga',
'Avenue',
'Bay',
'Boardwalk',
'Boulevard',
'Brae',
'Broadway',
'Bypass',
'Carrowmore',
'Central',
'Circle',
'Circus',
'Close',
'Common',
'Concourse',
'Court',
'Courtneys',
'Cove',
'Crescent',
'Crest',
'Dale',
'Dell',
'Downs',
'Drive',
'Earlsway',
'East',
'Enclave',
'End',
'Esplanade',
'Extension',
'Fairway',
'Fen',
'Furlong',
'Garden',
'Gardens',
'Glade',
'Glebe',
'Glen',
'Goldfield',
'Green',
'Greens',
'Grove',
'Gully',
'Harbour',
'Haven',
'Heights',
'Highway',
'Hill',
'Hilltop',
'Ho',
'Homestead',
'Island',
'Isle',
'Kingsway',
'Knoll',
'Landing',
'Lane',
'Lea',
'Link',
'Lochview',
'Loop',
'Mall',
'Meadway',
'Mermaid',
'Mews',
'Mile',
'Motu',
'Neptune',
'Nook',
'North',
'Oaks',
'Oasis',
'Oho',
'Oval',
'Parade',
'Park',
'Parkway',
'Pass',
'Place',
'Plaza',
'Point',
'Priors',
'Prom',
'Promenade',
'Quadrant',
'Queensway',
'Reach',
'Retreat',
'Ridge',
'Rise',
'Ritz',
'Road',
'Rosebowl',
'Row',
'Serpentine',
'Settlement',
'Silverfield',
'Slope',
'South',
'Spa',
'Spinney',
'Spur',
'Square',
'Strand',
'Street',
'Tahuhu',
'Tai',
'Terrace',
'Track',
'Trail',
'Trees',
'Treeway',
'Vale',
'Valley',
'View',
'Views',
'Walk',
'Watch',
'Waterways',
'Way',
'West',
'Willowbrook',
"d'Amarres"}
%time
addresses_tree = cKDTree(np.array(list(addresses[~addresses.geometry.isna()].geometry.apply(lambda x: (x.x, x.y)))))
bcs_numbered_tree = cKDTree(np.array(list(bcs_numbered.geometry.apply(lambda x: (x.x, x.y)))))
bcs_ranged_tree = cKDTree(np.array(list(bcs_ranged.geometry.apply(lambda x: (x.x, x.y)))))
CPU times: user 5 µs, sys: 1 µs, total: 6 µs Wall time: 10.3 µs
# test some radii to see which is suitable
bcs_numbered_sample = bcs_numbered.sample(1000)
bcs_numbered_sample_tree = cKDTree(np.array(list(bcs_numbered_sample.geometry.apply(lambda x: (x.x, x.y)))))
matches = {}
for r in [10, 50, 100, 175] + list(range(250, 3251, 250)):
matches[r] = []
# list of lists: ith sub list contains indices of use_tree points within r of the ith data_tree point
bcs_numbered_neighbours = bcs_numbered_sample_tree.query_ball_tree(addresses_tree, r)
for i, neighbours in tqdm(enumerate(bcs_numbered_neighbours)):
# check how many matches there are
matches[r].append(np.sum(bcs_numbered_sample.iloc[i].number_name == addresses.iloc[neighbours].number_name))
# for r in sorted(matches.keys()):
# print('####', r, '####')
# print('match:', np.sum(np.array(matches[r]) == 1) / len(matches[r]))
# print('ambiguous:', np.sum(np.array(matches[r]) > 1) / len(matches[r]))
# print('no match:', np.sum(np.array(matches[r]) == 0) / len(matches[r]))
r_list = list(sorted(matches.keys()))
ax = plt.subplot(1,1,1)
ax.plot(r_list, [np.sum(np.array(matches[r]) == 1) / len(matches[r]) for r in r_list], label='match')
ax.plot(r_list, [np.sum(np.array(matches[r]) > 1) / len(matches[r]) for r in r_list], label='ambiguous')
ax.plot(r_list, [np.sum(np.array(matches[r]) == 0) / len(matches[r]) for r in r_list], label='no match')
ax.legend()
plt.xlabel('radius')
plt.ylabel('proportion')
plt.grid()
# test some radii to see which is suitable
def unique_radius_column_match(left, right, left_col, right_col, r=1250, left_tree=None, right_tree=None):
"""find points in right that are within r of points in left and uniquely match on a column"""
if right_tree is None:
right_tree = cKDTree(np.array(list(right.geometry.apply(lambda x: (x.x, x.y)))))
if left_tree is None:
left_tree = cKDTree(np.array(list(left.geometry.apply(lambda x: (x.x, x.y)))))
# specify columns and initialize neighbour information dictionary if using the less efficient method
# columns = ('address_id', 'full_address', 'geometry')
# neighbour_information = {c: [] for c in columns}
match_indices = []
# list of lists: ith sub list contains indices of right points within r of the ith left point
left_tree_neighbours = left_tree.query_ball_tree(addresses_tree, r)
for i, neighbours in tqdm(enumerate(left_tree_neighbours), total=len(left_tree_neighbours)):
# subset right to those within r distance
right_neighbours = addresses.iloc[neighbours]
# check how many matches there are
match_indicator = left.iloc[i][left_col] == right_neighbours[right_col]
if np.sum(match_indicator) == 1:
# extract the index from right_neighbours
# extracting the index only and later subsetting the full addresses dataframe
# results in a ~4x speed up over extracting the requisite addresses information within this for loop
match_indices.append(right_neighbours[match_indicator].index[0])
elif np.sum(match_indicator) > 1:
match_indices.append(-1)
else:
match_indices.append(-2)
# print('####', r, '####')
# print('match:', np.sum(np.array(match_indices) > -1) / len(match_indices))
# print('ambiguous:', np.sum(np.array(match_indices) == -1) / len(match_indices))
# print('no match:', np.sum(np.array(match_indices) == -2) / len(match_indices))
return match_indices
# add empty row with index -1; this will be retrieved if there is no match address match
addresses.loc[-1] = addresses.loc[1]
for c in addresses.columns:
addresses.loc[-1, c] = np.nan
addresses.loc[-1, c]
addresses.loc[-2] = addresses.loc[-1]
bcs_numbered_sample = bcs_numbered.sample(100)
addresses.loc[match_indices].geometry.reset_index(drop=True)
0 POINT (19445145.208 -4381779.063) 1 POINT (19431050.314 -4406402.836) 2 POINT (19451295.075 -4424310.520) 3 POINT (19472792.559 -4428320.864) 4 None 5 POINT (19451798.838 -4402948.354) 6 POINT (19448871.690 -4388815.649) 7 None 8 POINT (19441001.992 -4419707.478) 9 POINT (19471097.591 -4435539.799) Name: geometry, dtype: geometry
r = 1250
match_col = 'number_name'
match_indices = unique_radius_column_match(bcs_numbered_sample, addresses, match_col, match_col, r=r, right_tree=addresses_tree)
bcs_numbered_sample['addresses_geometry'] = addresses.loc[match_indices].geometry.reset_index(drop=True).tolist()
bcs_numbered_sample['addresses_full_address'] = addresses.loc[match_indices].full_address.reset_index(drop=True).tolist()
bcs_numbered_sample['euc_distance'] = [np.sqrt((a[0] - b[0]) ** 2 + (a[1] - b[1]) ** 2) for a, b in zip(bcs_numbered_sample.geometry.apply(lambda x: (x.x, x.y)), bcs_numbered_sample['addresses_geometry'].apply(lambda x: (x.x, x.y) if not pd.isna(x) else (0, 0)))]
bcs_numbered_sample[~bcs_numbered_sample.addresses_full_address.isna()].sort_values('euc_distance').reset_index(drop=True).euc_distance.plot()
plt.xlabel('Building Consent')
plt.ylabel('Distance to Matched Address (metres)')
Text(0, 0.5, 'Distance to Matched Address (metres)')
with pd.option_context('display.max_rows', None, 'display.max_columns', None):
display(bcs_numbered_sample.sort_values('euc_distance')[['full_address', 'addresses_full_address']])
| full_address | addresses_full_address | |
|---|---|---|
| 11910 | 302 buckland road mangere east auckland | 302 Buckland Road, Mangere East, Auckland |
| 14559 | 15f wonderview road leigh auckland | 15F Wonderview Road, Leigh |
| 23042 | 41 sunshine boulevard sunnyvale auckland | 41 Sunshine Boulevard, Sunnyvale, Auckland |
| 27621 | 5 cory road waiheke island auckland | 5 Cory Road, Palm Beach, Waiheke Island |
| 12352 | 22 aorangi place birkenhead auckland | 22 Aorangi Place, Birkenhead, Auckland |
| 27689 | 75 litten road cockle bay auckland | 75 Litten Road, Cockle Bay, Auckland |
| 1255 | 2 hakanoa street grey lynn auckland | 2 Hakanoa Street, Grey Lynn, Auckland |
| 7325 | 26 longfellow parade titirangi auckland | 26 Longfellow Parade, Glen Eden, Auckland |
| 5807 | 2 riverside drive waiuku auckland | 2 Riverside Drive, Waiuku |
| 4631 | 40 rimu road manurewa auckland | 40 Rimu Road, Manurewa, Auckland |
| 25134 | 8 paneika lane otahuhu auckland | 8 Paneika Lane, Otahuhu, Auckland |
| 1597 | 141 boundary road papakura auckland | 141 Boundary Road, Papakura |
| 6980 | 30 boocock crescent orewa auckland | 30 Boocock Crescent, Orewa |
| 25427 | 1 three oaks drive dairy flat auckland | 1 Three Oaks Drive, Dairy Flat |
| 22942 | 23 dinning road riverhead auckland | 23 Dinning Road, Riverhead |
| 26829 | 1 campbell road maraetai auckland | 1 Campbell Road, Maraetai, Auckland |
| 19203 | 88 godfrey drive silverdale auckland | 88 Godfrey Drive, Orewa |
| 89353 | 9 may ave te atatu south | 9 May Avenue, Te Atatu South, Auckland |
| 65938 | 17 arion rd takanini | 17 Arion Road, Takanini |
| 53063 | 20 whaley greens silverdale silverdale | 20 Whaley Greens, Silverdale |
| 179340 | 53 motutapu ave manly | 53 Motutapu Avenue, Manly, Whangaparaoa |
| 64497 | 13 wisteria lane silverdale | 13 Wisteria Lane, Silverdale |
| 147021 | 6 aldern rd massey | 6 Aldern Road, Massey, Auckland |
| 60541 | 2 conti dr flat bush | 2 Conti Drive, Flat Bush, Auckland |
| 78764 | 3 ruze vida dr massey | 3 Ruze Vida Drive, Massey, Auckland |
| 35010 | 8 bomber lane whenuapai auckland | 8 Bomber Lane, Whenuapai, Auckland |
| 149815 | 24 chateau rise east tamaki | 24 Chateau Rise, Flat Bush, Auckland |
| 54053 | 50 golden morning dr albany heights 0632 | 50 Golden Morning Drive, Albany Heights, Auckland |
| 46805 | 52 pohutukawa parade riverhead | 52 Pohutukawa Parade, Riverhead |
| 46381 | 3 lagonda rise oteha | 3 Lagonda Rise, Oteha, Auckland |
| 42674 | 16 hakaro way takanini | 16 Hakaro Way, Takanini |
| 141546 | 13 bejoy rise howick | 13 Bejoy Rise, East Tamaki Heights, Auckland |
| 168598 | 18a newell st point chevalier | 18A Newell Street, Point Chevalier, Auckland |
| 176966 | 4 sonterra close manurewa | 4 Sonterra Close, Randwick Park, Auckland |
| 51613 | 18 hooton drive huapai huapai | 18 Hooton Drive, Kumeu |
| 165221 | 7 fintry pl flat bush | 7 Fintry Place, Flat Bush, Auckland |
| 67261 | 38 ashlynne ave papatoetoe | 38 Ashlynne Avenue, Papatoetoe, Auckland |
| 112620 | 108 picasso dr west harbour | 108 Picasso Drive, West Harbour, Auckland |
| 104439 | 105 duck creek rd stillwater | 105 Duck Creek Road, Stillwater |
| 180331 | 7 wharf st papakura | 7 Wharf Street, Papakura |
| 158528 | 90 kittiwake dr albany | 90 Kittiwake Drive, Schnapper Rock, Auckland |
| 159272 | 44 ploughmans ave pukekohe | 44 Ploughmans Avenue, Pukekohe |
| 35124 | 39 rautahi terrace redvale auckland | 39 Rautahi Terrace, Redvale, Auckland |
| 79595 | 43 james st mangere | 43 James Street, Mangere East, Auckland |
| 152618 | 50 tiriwa dr massey | 50 Tiriwa Drive, Massey, Auckland |
| 174996 | 3 springtide pl red beach | 3 Springtide Place, Red Beach |
| 149027 | 30 lynton rd mt wellington | 30 Lynton Road, Mount Wellington, Auckland |
| 194750 | 3 feeny cres east tamaki | 3 Feeny Crescent, East Tamaki, Auckland |
| 72140 | 43 landing dr albany | 43 Landing Drive, Albany, Auckland |
| 66497 | 96a harris street pukekohe | 96A Harris Street, Pukekohe |
| 203287 | 30 premila dr pukekohe | 30 Premila Drive, Pukekohe |
| 88891 | 8 pono pl waiuku | 8 Pono Place, Waiuku |
| 126494 | 4 argyle ave papakura | 4 Argyle Avenue, Pahurehure, Papakura |
| 125141 | 1 highwood grove torbay | 1 Highwood Grove, Torbay, Auckland |
| 29981 | 21 derrimore heights clover park auckland | 21 Derrimore Heights, Clover Park, Auckland |
| 163003 | 3 dellwood ave henderson | 3 Dellwood Avenue, Henderson, Auckland |
| 114145 | 5 rawson way takanini | 5 Rawson Way, Takanini |
| 94953 | 2/21 aorangi pl birkenhead | 2/21 Aorangi Place, Birkenhead, Auckland |
| 167225 | 89 mt lebanon cres manurewa | 89 Mt Lebanon Crescent, The Gardens, Auckland |
| 173149 | 7 drumbeg close flat bush | 7 Drumbeg Close, Flat Bush, Auckland |
| 78888 | 9 blanc rd silverdale | 9 Blanc Road, Silverdale |
| 106131 | 16 te hoe grove albany | 16 Te Hoe Grove, Pinehill, Auckland |
| 31267 | 91 karepiro drive stanmore bay auckland | 91 Karepiro Drive, Stanmore Bay, Whangaparaoa |
| 108386 | 95 atkin ave mission bay | 95 Atkin Avenue, Mission Bay, Auckland |
| 146677 | 26 duncansby rd stanmore bay | 26 Duncansby Road, Stanmore Bay, Whangaparaoa |
| 45224 | 2 gallantry crescent papakura | 2 Gallantry Crescent, Papakura |
| 154737 | 74 rangitoto rd papatoetoe | 74 Rangitoto Road, Papatoetoe, Auckland |
| 169823 | 39 hanene st st heliers | 39 Hanene Street, St Heliers, Auckland |
| 116371 | 57 picasso dr west harbour | 57 Picasso Drive, West Harbour, Auckland |
| 64541 | 50 corricvale way northcross | 50 Corricvale Way, Northcross, Auckland |
| 31281 | 2 valley road browns bay auckland | 2 Valley Road, Browns Bay, Auckland |
| 170825 | 1286 harvard lane ardmore | 1286 Harvard Lane, Ardmore |
| 185465 | 83 rathmar dr manurewa | 83 Rathmar Drive, Manurewa, Auckland |
| 101749 | 50 mountain rd mangere bridge | 50 Mountain Road, Mangere Bridge, Auckland |
| 194723 | 42 lady ruby dr east tamaki | 42 Lady Ruby Drive, East Tamaki, Auckland |
| 97647 | 8 robley cres glendowie | 8 Robley Crescent, Glendowie, Auckland |
| 123801 | 8 victory rd laingholm | 8 Victory Road, Laingholm, Auckland |
| 142017 | 74 coxhead rd manurewa | 74 Coxhead Road, Manurewa, Auckland |
| 102538 | 114 allum st kohimarama | 114 Allum Street, Kohimarama, Auckland |
| 179111 | 13a halver rd manurewa | 13A Halver Road, Hillpark, Auckland |
| 173036 | 70 walters rd papakura | 70 Walters Road, Takanini |
| 166356 | 12 mccallum dr sandspit | 12 McCallum Drive, Sandspit |
| 86995 | 584 blockhouse bay rd blockhouse bay | 584 Blockhouse Bay Road, Blockhouse Bay, Auckland |
| 36587 | 131 martyn wright road pukekohe auckland | 131 Martyn Wright Road, Mauku |
| 179618 | 5 georgina st freemans bay | 5 Georgina Street, Freemans Bay, Auckland |
| 138664 | 275 ormiston rd flat bush | 275 Ormiston Road, Flat Bush, Auckland |
| 48660 | 444b peach hill road drury 0 | 444B Peach Hill Road, Hunua |
| 141993 | 77 gearon rd waiuku | 77 Gearon Road, Mauku |
| 170063 | 74 mangere rd otahuhu | 74 Mangere Road, Otahuhu, Auckland |
| 180116 | 1 otanerua rd hatfields beach | NaN |
| 73814 | 531 whitmore rd matakana | NaN |
| 167989 | 45 new windsor rd avondale | NaN |
| 152383 | 9 clarke rd royal oak | NaN |
| 150907 | 0 karioitahi rd waiuku | NaN |
| 65901 | 2 o'rorke rd penrose | NaN |
| 114640 | 130 bucklands beach rd bucklands beach | NaN |
| 205755 | 10 watson pl | NaN |
| 13441 | 303 flat bush school road flat bush auckland | NaN |
| 93924 | 16 medvale ave otara | NaN |
| 63351 | 149b sandhills rd great barrier island | NaN |
addresses_no_na[addresses_no_na.full_address.str.contains('^[0-9]*\/303 Flat Bush', regex=True)]
| address_id | change_id | address_type | unit_value | address_number | address_number_suffix | address_number_high | water_route_name | water_name | suburb_locality | town_city | full_address_number | full_road_name | full_address | road_section_id | gd2000_xcoord | gd2000_ycoord | water_route_name_ascii | water_name_ascii | suburb_locality_ascii | town_city_ascii | full_road_name_ascii | full_address_ascii | geometry | number_name |
|---|
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^300 Flat', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[13441]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^130 Bucklands Beach', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[114640]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^8 Clarke Road, Oneh', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[167989]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)
# no exact 45 - needs to have a flat
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^[0-9]*\/45 New Windsor', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[167989]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)
# no 531, even with a flat
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^51. Whitmore', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[73814]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)
# way off
addresses_no_na = addresses[~addresses.full_address.isna()]
address_points = addresses_no_na[addresses_no_na.full_address.str.contains('^1 Otanerua', regex=True)].assign(source='address coordinate')
bcs_points = bcs.loc[[180116]].assign(source='building consent coordinate')
ax = pd.concat([address_points[['source', 'geometry']], bcs_points[['source', 'geometry']]]).plot(column='source', legend=True)
plt.xlim(bounds3857[0], bounds3857[2])
plt.ylim(bounds3857[1], bounds3857[3])
plt.title(address_points.full_address.values[0] + ' / ' + bcs_points.full_address.values[0])
ctx.add_basemap(ax)
def range_expand(r):
if '/' in r:
r, suffix = r.split('/')
suffix = '/' + suffix
else:
suffix = ''
r1, r2 = r.split('-')
if r1.isdecimal() and r2.isdecimal():
return [str(i) + suffix for i in range(int(r1), int(r2) + 1)]
else:
return r + suffix
ranged_list = []
for index, row in tqdm(bcs_ranged.iterrows(), total=len(bcs_ranged)):
number_name = row.number_name.split(' ')
number = number_name[0]
name = number_name[1]
range_expanded = range_expand(number)
range_rows = [row.copy() for _ in range(len(range_expanded))]
for i, rr in enumerate(range_rows):
rr.number_name = range_expanded[i] + ' ' + name
ranged_list.append(pd.DataFrame(range_rows))
# number of building consents that don't have NA for ADDRESS_1, and also don't have an address starting with a number
len([a for a in bcs[~bcs.ADDRESS_1.isna()].ADDRESS_1 if not a[0] in [str(i) for i in range(10)]])
3725
%%time
nz_parcels = gpd.read_file('input/lds-nz-primary-parcels-FGDB.zip!nz-primary-parcels.gdb')
print(nz_parcels.crs)
nz_parcels = nz_parcels.to_crs(3857)
nz_parcels = nz_parcels.cx[bounds_buffered3857[0]:bounds_buffered3857[2], bounds_buffered3857[1]:bounds_buffered3857[3]]
epsg:4167 CPU times: user 3min 58s, sys: 1.15 s, total: 3min 59s Wall time: 3min 59s
nz_parcels = nz_parcels.cx[bounds_buffered3857[0]:bounds_buffered3857[2], bounds_buffered3857[1]:bounds_buffered3857[3]]
nz_parcels
| id | appellation | affected_surveys | parcel_intent | topology_type | statutory_actions | land_district | titles | survey_area | calc_area | geometry | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 5266447 | None | None | Hydro | Primary | None | North Auckland | None | NaN | 1289690.0 | MULTIPOLYGON (((-12155911.950 495830.979, -121... |
| 1 | 4789727 | Part Lot 3 Allot 64 Section 1 SBRS OF Auckland | SO 663 | DCDB | Primary | None | North Auckland | None | NaN | 1.0 | MULTIPOLYGON (((-12296010.220 369113.384, -122... |
| 2 | 4810316 | Part Tidal Lands of Manukau Harbour Survey Off... | SO 67474 | DCDB | Primary | [Create] Revested in the Crown Sec 5 Foreshore... | North Auckland | None | 31600000.0 | 31342451.0 | MULTIPOLYGON (((-12349176.218 373759.767, -123... |
| 3 | 4817943 | Crown Land Survey Office Plan 58175 | SO 58175 | DCDB | Primary | [Create] Crown Land Reserved from Sale Sec 58 ... | North Auckland | None | NaN | 467490.0 | MULTIPOLYGON (((-12183771.380 518898.007, -121... |
| 4 | 4827816 | Lot 2 DP 165098 | DP 165098 | DCDB | Primary | None | North Auckland | NA99B/977 | 22979.0 | 22972.0 | MULTIPOLYGON (((-12254746.365 394243.168, -122... |
| 5 | 4937273 | Lot 2 DP 38555 | DP 38555 | DCDB | Primary | None | North Auckland | NA7A/846 | 726.0 | 727.0 | MULTIPOLYGON (((-12311163.341 359403.649, -123... |
| 6 | 4991598 | Lot 161 DP 6975 | DP 6975 | DCDB | Primary | [Create] Historic Reserve [Kawau Island Histor... | North Auckland | None | 1012.0 | 1045.0 | MULTIPOLYGON (((-12174704.920 398868.292, -121... |
| 7 | 5008028 | Lot 3 DP 155195 | DP 155195 | DCDB | Primary | None | North Auckland | NA92C/966, NA94A/279 | 12.0 | 12.0 | MULTIPOLYGON (((-12306276.415 367050.209, -123... |
| 8 | 5014167 | Crown Land | None | DCDB | Primary | [Create] Riverbank Reserve Sec 129 Land Act 1924 | North Auckland | None | NaN | 181271.0 | MULTIPOLYGON (((-12225342.686 475656.177, -122... |
| 9 | 5030697 | Lot 4 DP 61316 | DP 61316 | DCDB | Primary | None | North Auckland | NA18B/1061 | 5.0 | 5.0 | MULTIPOLYGON (((-12303511.996 347847.490, -123... |
| 10 | 5266127 | None | None | Hydro | Primary | None | North Auckland | None | NaN | 258258.0 | MULTIPOLYGON (((-12221698.709 476727.407, -122... |
| 11 | 5043516 | Part Tidal Lands of Manukau Harbour Survey Off... | SO 67474 | DCDB | Primary | [Create] Revested in the Crown Sec 5 Foreshore... | North Auckland | None | 802300.0 | 778257.0 | MULTIPOLYGON (((-12342742.430 390472.136, -123... |
| 12 | 5074709 | Crown Land Survey Office Plan 44403 | SO 44403 | DCDB | Primary | [Create] Crown Land Reserved from Sale (Margin... | North Auckland | None | NaN | 127235.0 | MULTIPOLYGON (((-12199997.005 521092.551, -121... |
| 13 | 5116430 | Lot 2 DP 58587 | DP 58587 | DCDB | Primary | None | North Auckland | NA13B/1218 | 13.0 | 12.0 | MULTIPOLYGON (((-12303179.527 373018.829, -123... |
| 14 | 5122300 | Lot 3 DP 22134 | DP 22134 | DCDB | Primary | None | North Auckland | NA610/117 | 3.0 | 2.0 | MULTIPOLYGON (((-12296958.755 372226.919, -122... |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 537275 | 8022000 | Lot 1 DP 522526 | DP 522526 | Fee Simple Title | Primary | None | North Auckland | 830418 | 234875.0 | 234901.0 | MULTIPOLYGON (((-12309085.450 290487.154, -123... |
| 537276 | 7264068 | Lot 615 DP 429024 | DP 429024, LT 450627 | Fee Simple Title | Primary | None | North Auckland | 513806 | 447.0 | 447.0 | MULTIPOLYGON (((-12336992.097 315398.428, -123... |
| 537277 | 7264056 | Lot 51 DP 429024 | DP 429024 | Fee Simple Title | Primary | None | North Auckland | 513825, 513826, 513827, 513828, 513829, 513830... | 1772.0 | 1771.0 | MULTIPOLYGON (((-12337109.091 315547.825, -123... |
| 537278 | 7190462 | Lot 548 DP 420258 | DP 420258 | Fee Simple Title | Primary | None | North Auckland | 483135 | 341.0 | 341.0 | MULTIPOLYGON (((-12336188.725 314781.089, -123... |
| 537279 | 7266272 | Lot 536 DP 427884 | DP 427884, LT 454543 | Fee Simple Title | Primary | None | North Auckland | 520878 | 338.0 | 338.0 | MULTIPOLYGON (((-12336035.238 314657.366, -123... |
| 537280 | 7264079 | Lot 626 DP 429024 | DP 429024 | Fee Simple Title | Primary | None | North Auckland | 513817 | 307.0 | 307.0 | MULTIPOLYGON (((-12336879.505 315659.652, -123... |
| 537281 | 5130443 | Lot 30 DP 17583 | DP 17583, DP 531059 | DCDB | Primary | None | North Auckland | 865806, NA78B/972 | 813.0 | 813.0 | MULTIPOLYGON (((-12317776.688 369234.849, -123... |
| 537282 | 7535538 | Lot 88 DP 469841 | DP 469841 | Fee Simple Title | Primary | None | North Auckland | 634066 | 389.0 | 389.0 | MULTIPOLYGON (((-12336254.897 315319.544, -123... |
| 537283 | 7264099 | Lot 646 DP 429024 | DP 429024 | Fee Simple Title | Primary | None | North Auckland | 513837 | 151.0 | 151.0 | MULTIPOLYGON (((-12336903.659 315801.323, -123... |
| 537284 | 8054279 | Lot 24 DP 533358 | DP 533358 | Fee Simple Title | Primary | None | North Auckland | 877006 | 229.0 | 229.0 | MULTIPOLYGON (((-12288815.890 400558.457, -122... |
| 537285 | 7291940 | Lot 745 DP 433546 | DP 433546 | Fee Simple Title | Primary | None | North Auckland | 528968 | 247.0 | 247.0 | MULTIPOLYGON (((-12336398.380 315153.542, -123... |
| 537286 | 7266269 | Lot 533 DP 427884 | DP 427884, LT 454543 | Fee Simple Title | Primary | None | North Auckland | 520875 | 336.0 | 336.0 | MULTIPOLYGON (((-12335989.801 314781.682, -123... |
| 537287 | 8051540 | Lot 8 DP 533517 | DP 533517 | Fee Simple Title | Primary | None | North Auckland | 876934 | 185.0 | 185.0 | MULTIPOLYGON (((-12231484.279 417503.744, -122... |
| 537288 | 7264065 | Lot 518 DP 429024 | DP 429024 | Fee Simple Title | Primary | None | North Auckland | 513803 | 313.0 | 313.0 | MULTIPOLYGON (((-12336358.450 315254.637, -123... |
| 537289 | 7266268 | Lot 532 DP 427884 | DP 427884, LT 454543 | Fee Simple Title | Primary | None | North Auckland | 520874 | 331.0 | 331.0 | MULTIPOLYGON (((-12335919.436 314757.471, -123... |
537290 rows × 11 columns
sa2 = gpd.read_file('NZ-SA/statistical-area-2-2020-generalised.gdb')
sa2 = sa2.to_crs(3857)
gpd.sjoin(bc_sample, sa2)
| OBS | CONSENT_DATE | MARCH_YEAR | ADDRESS_1 | ADDRESS_2 | ADDRESS_3 | LB_Name | BUILDING_TYPE_NAME | BUILDING_TYPE_CODE | FLOOR_AREA | VALUE | BUILDINGS | Building_Type_Group | Residential_Type | Business_Group | Business_Category | Type_Class_Year | MBCODE | MBYEAR | AUP_BaseZone | AUP_BaseZone_Group | ZONE | ZONE_ID | ZONE_NAME | ZONE_TYPE | BUSINESS_TYPE | PlanAreaName | SHA_Name_154 | X_Coordinate | Y_Coordinate | geometry | index_right | SA22020_V1_00 | SA22020_V1_00_NAME | SA22020_V1_00_NAME_ASCII | LAND_AREA_SQ_KM | AREA_SQ_KM | SHAPE_Length | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 6440 | 219437 | Aug 01, 2020 12:00:00 AM | Mar 01, 2020 12:00:00 AM | 13 GRIFFEN PARK ROAD | Mount Roskill | Auckland | Puketapapa | Townhouses, flats, units, and other dwellings | 1129 | 634.0 | NaN | 3 | Residential | Attached | NaN | NaN | 2014 | 587000.0 | 2013.0 | Residential - Terrace Housing and Apartment Bu... | Residential | 8.0 | ACC_24 | Residential 6a | Residential | NaN | NaN | NaN | 174.719937 | -36.920856 | POINT (19449734.429 -4428080.845) | 1006 | 139600 | Lynfield North | Lynfield North | 0.940925 | 0.940925 | 5537.544865 |
| 203270 | 65577 | Mar 01, 2000 12:00:00 AM | Apr 01, 2000 12:00:00 AM | DUNN RD | DRURY | Franklin | New (and pre-built) house, unit, bach, crib, b... | 1100 | 207.0 | 95000.0 | 1 | Residential | Detached | NaN | NaN | 1998 | 815401.0 | 2013.0 | Road | General | 27.0 | NaN | NaN | NaN | NaN | NaN | NaN | 175.052449 | -37.151529 | POINT (19486749.463 -4460249.110) | 1233 | 166400 | Ararimu | Ararimu | 90.925442 | 90.925442 | 58867.417446 | |
| 66156 | 144992 | May 01, 2013 12:00:00 AM | Mar 01, 2013 12:00:00 AM | 39 | BEACHWOOD DR | HATFIELDS BEACH | Hibiscus and Bays | New (and pre-built) house, unit, bach, crib, b... | 1100 | 203.0 | 354000.0 | 1 | Residential | Detached | NaN | NaN | 1998 | 170807.0 | 2013.0 | Residential - Single House Zone | Residential | 19.0 | RDC_27 | Residential Medium Inten | Residential | NaN | NaN | NaN | 174.686288 | -36.567512 | POINT (19445988.651 -4378993.739) | 621 | 112800 | Hatfields Beach | Hatfields Beach | 0.923252 | 0.923252 | 4314.585524 |
| 91368 | 39665 | Jan 01, 1997 12:00:00 AM | Apr 01, 1997 12:00:00 AM | 45 | PALOMINO DR | WESTERN HEIGHTS | Henderson - Massey | New (and pre-built) house, unit, bach, crib, b... | 1100 | 210.0 | 100000.0 | 1 | Residential | Detached | NaN | NaN | 1998 | 281514.0 | 2013.0 | Residential - Mixed Housing Urban Zone | Residential | 60.0 | WCC_7 | Living | Residential | NaN | NaN | NaN | 174.623138 | -36.898135 | POINT (19438958.803 -4424917.654) | 908 | 128300 | McLaren Park | McLaren Park | 1.088384 | 1.088384 | 4957.562154 |
| 10567 | 211720 | Mar 01, 2020 12:00:00 AM | Mar 01, 2020 12:00:00 AM | 61 CLARK ROAD | Hobsonville | Auckland | Upper Harbour | Townhouses, flats, units, and other dwellings | 1129 | 95.0 | 240000.0 | 1 | Residential | Attached | NaN | NaN | 2014 | 222800.0 | 2013.0 | Residential - Mixed Housing Urban Zone | Residential | 60.0 | WCC_16 | Special Area | Special | NaN | Scott Point, Sunderland Precinct, Hobsonville ... | Scott Point, Sunderland Precinct, Hobsonville ... | 174.654705 | -36.799413 | POINT (19442472.770 -4411184.328) | 71 | 120200 | Hobsonville Point | Hobsonville Point | 3.521568 | 3.521568 | 11979.226387 |